package com.lfs.admin.util;

import com.lfs.admin.model.entity.dataPackage.DataUploadImageEntity;
import com.lfs.admin.model.report.OrderTrade;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

public class ExeclUtil {

    public static void createSumExportHead(XSSFSheet sheet) {
        XSSFRow row = sheet.createRow(0);

        XSSFCell cell = row.createCell(0);
        cell.setCellValue("商户账号");
        sheet.setColumnWidth(0, 14 * 256);

        cell = row.createCell(1);
        cell.setCellValue("商户名称");
        sheet.setColumnWidth(1, 28 * 256);

        cell = row.createCell(2);
        cell.setCellValue("收款账号");
        sheet.setColumnWidth(2, 28 * 256);

        cell = row.createCell(3);
        cell.setCellValue("成功笔数");
        sheet.setColumnWidth(3, 14 * 256);

        cell = row.createCell(4);
        cell.setCellValue("成功交易额(元)");
        sheet.setColumnWidth(4, 14 * 256);

        cell = row.createCell(5);
        cell.setCellValue("成功扣款额(元)");
        sheet.setColumnWidth(5, 14 * 256);

        cell = row.createCell(6);
        cell.setCellValue("失败笔数");
        sheet.setColumnWidth(6, 18 * 256);

        cell = row.createCell(7);
        cell.setCellValue("失败交易金额(元)");
        sheet.setColumnWidth(7, 18 * 256);

        cell = row.createCell(8);
        cell.setCellValue("应付客户总金额(元)");
        sheet.setColumnWidth(8, 18 * 256);

        cell = row.createCell(9);
        cell.setCellValue("应付上游总金额(元)");
        sheet.setColumnWidth(9, 18 * 256);

        cell = row.createCell(10);
        cell.setCellValue("总收入金额(元)");
        sheet.setColumnWidth(10, 18 * 256);

        cell = row.createCell(11);
        cell.setCellValue("总入账费用(元)");
        sheet.setColumnWidth(11, 18 * 256);
    }

    public static void createFinanceExportHead(XSSFSheet sheet) {
        XSSFRow row = sheet.createRow(0);

        XSSFCell cell = row.createCell(0);
        cell.setCellValue("商户账号");
        sheet.setColumnWidth(0, 14 * 256);

        cell = row.createCell(1);
        cell.setCellValue("商户名称");
        sheet.setColumnWidth(1, 28 * 256);

        cell = row.createCell(2);
        cell.setCellValue("成功总笔数");
        sheet.setColumnWidth(2, 14 * 256);

        cell = row.createCell(3);
        cell.setCellValue("成功总交易额(元)");
        sheet.setColumnWidth(3, 14 * 256);

        cell = row.createCell(4);
        cell.setCellValue("转账金额(元)");
        sheet.setColumnWidth(4, 14 * 256);

        cell = row.createCell(5);
        cell.setCellValue("费率");
        sheet.setColumnWidth(5, 18 * 256);

        cell = row.createCell(6);
        cell.setCellValue("上游金额(元)");
        sheet.setColumnWidth(6, 18 * 256);

        cell = row.createCell(7);
        cell.setCellValue("入账金额(元)");
        sheet.setColumnWidth(7, 18 * 256);

        cell = row.createCell(8);
        cell.setCellValue("上游渠道名");
        sheet.setColumnWidth(8, 18 * 256);

    }

    public static void createSumExportContext(XSSFWorkbook workbook, XSSFSheet sheet, List<OrderTrade> orderList) {

        long stotal = 0;
        BigDecimal smoney = new BigDecimal(0);
        BigDecimal soutmoney = new BigDecimal(0);
        BigDecimal sInmoney = new BigDecimal(0);
        long ftotal = 0;
        BigDecimal fmoney = new BigDecimal(0);
        BigDecimal succOutMoney = new BigDecimal(0);
        BigDecimal profitTotal = new BigDecimal(0);
        BigDecimal inPriceTotal = new BigDecimal(0);
        for (int i = 1; i < orderList.size() + 1; i++) {
            OrderTrade order = orderList.get(i - 1);
            XSSFRow row = sheet.createRow(i);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue(order.getAgtPhone());
            cell = row.createCell(1);
            cell.setCellValue(order.getAgtName());
            cell = row.createCell(2);
            cell.setCellValue(order.getChargeAddr());
            cell = row.createCell(3);
            cell.setCellValue(order.getSuccTotal());
            cell = row.createCell(4);
            cell.setCellValue(order.getSuccTotalPrice() + "");
            cell = row.createCell(5);
            cell.setCellValue(order.getSucctotalOutMoney() + "");
            cell = row.createCell(6);
            cell.setCellValue(order.getFailTotal());
            cell = row.createCell(7);
            cell.setCellValue(order.getFailTotalPrice() + "");
            cell = row.createCell(8);
            cell.setCellValue(order.getSucctotalOutMoney() + "");
            cell = row.createCell(9);
            cell.setCellValue(order.getSucctotalInMoney() + "");
            cell = row.createCell(10);
            cell.setCellValue(order.getSuccTotalPrice() + "");
            cell = row.createCell(11);
            cell.setCellValue(order.getProfit() + "");
            stotal += order.getSuccTotal();
            smoney = smoney.add(order.getSuccTotalPrice());
            soutmoney = soutmoney.add(order.getSucctotalOutMoney());
            sInmoney = sInmoney.add(order.getSucctotalInMoney());
            ftotal += order.getFailTotal();
            fmoney = fmoney.add(order.getFailTotalPrice());
            succOutMoney = succOutMoney.add(order.getSucctotalOutMoney());
            profitTotal = profitTotal.add(order.getProfit());
            inPriceTotal = inPriceTotal.add(order.getSuccTotalPrice());
        }
        XSSFRow row = sheet.createRow(orderList.size() + 2);

        CellStyle style = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 16);//设置字体大小
        style.setFont(font);

        XSSFCell cel0 = row.createCell(0);
        cel0.setCellValue("总计");
        cel0.setCellStyle(style);

        cel0 = row.createCell(3);
        cel0.setCellValue(stotal);
        cel0.setCellStyle(style);

        cel0 = row.createCell(4);
        cel0.setCellValue(smoney + "");
        cel0.setCellStyle(style);

        cel0 = row.createCell(5);
        cel0.setCellValue(soutmoney + "");
        cel0.setCellStyle(style);

        cel0 = row.createCell(6);
        cel0.setCellValue(ftotal + "");
        cel0.setCellStyle(style);

        cel0 = row.createCell(7);
        cel0.setCellValue(fmoney + "");
        cel0.setCellStyle(style);

        cel0 = row.createCell(8);
        cel0.setCellValue(soutmoney + "");
        cel0.setCellStyle(style);

        cel0 = row.createCell(9);
        cel0.setCellValue(sInmoney + "");
        cel0.setCellStyle(style);

        cel0 = row.createCell(10);
        cel0.setCellValue(inPriceTotal + "");
        cel0.setCellStyle(style);

        cel0 = row.createCell(11);
        cel0.setCellValue(profitTotal + "");
        cel0.setCellStyle(style);
    }

    public static void createFinanceContext(XSSFWorkbook workbook, XSSFSheet sheet, List<OrderTrade> orderList) {

        long stotal = 0;
        BigDecimal smoney = new BigDecimal(0);
        BigDecimal soutmoney = new BigDecimal(0);
        BigDecimal sInmoney = new BigDecimal(0);
        BigDecimal succOutMoney = new BigDecimal(0);
        BigDecimal profitTotal = new BigDecimal(0);
        BigDecimal inPriceTotal = new BigDecimal(0);
        for (int i = 1; i < orderList.size() + 1; i++) {
            OrderTrade order = orderList.get(i - 1);
            XSSFRow row = sheet.createRow(i);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue(order.getAgtPhone());
            cell = row.createCell(1);

            cell.setCellValue(order.getAgtName());
            cell = row.createCell(2);

            cell.setCellValue(order.getSuccTotal());
            cell = row.createCell(3);

            cell.setCellValue(order.getSuccTotalPrice() + "");
            cell = row.createCell(4);

            cell.setCellValue(order.getSucctotalOutMoney() + "");
            cell = row.createCell(5);

            cell.setCellValue(order.getProductProfite() + "");
            cell = row.createCell(6);

            cell.setCellValue(order.getSucctotalInMoney() + "");
            cell = row.createCell(7);

            cell.setCellValue(order.getProfit() + "");
            cell = row.createCell(8);

            cell.setCellValue(order.getChannelName() + "");
            cell = row.createCell(9);

            stotal += order.getSuccTotal();
            smoney = smoney.add(order.getSuccTotalPrice());
            sInmoney = sInmoney.add(order.getSucctotalInMoney());
            soutmoney = soutmoney.add(order.getSucctotalOutMoney());
            succOutMoney = succOutMoney.add(order.getSucctotalOutMoney());
            profitTotal = profitTotal.add(order.getProfit());
            inPriceTotal = inPriceTotal.add(order.getSuccTotalPrice());
        }
        XSSFRow row = sheet.createRow(orderList.size() + 2);

        CellStyle style = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 16);//设置字体大小
        style.setFont(font);

        XSSFCell cel0 = row.createCell(0);
        cel0.setCellValue("总计");
        cel0.setCellStyle(style);

        cel0 = row.createCell(2);
        cel0.setCellValue(stotal);
        cel0.setCellStyle(style);

        cel0 = row.createCell(3);
        cel0.setCellValue(smoney + "");
        cel0.setCellStyle(style);

        cel0 = row.createCell(4);
        cel0.setCellValue(soutmoney + "");
        cel0.setCellStyle(style);

        cel0 = row.createCell(6);
        cel0.setCellValue(sInmoney + "");
        cel0.setCellStyle(style);

        cel0 = row.createCell(7);
        cel0.setCellValue(profitTotal + "");
        cel0.setCellStyle(style);

    }



    //导出数据包仓库发布商品第六步的上传商品数据
    public static void exportExcel(HttpServletResponse response, List<DataUploadImageEntity> dataList, String filePath) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) { // 创建Workbook
            Sheet sheet = workbook.createSheet("Data"); // 创建Sheet

            // 创建表头
            Row headerRow = sheet.createRow(0);
            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            String[] headers = {"id", "detailId", "localPath"}; // 列名数组
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
            }

            // 填充数据
            int rowNum = 1;
            for (DataUploadImageEntity data : dataList) {
                Row row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(data.getId());
                row.createCell(1).setCellValue(data.getDetailId());
                row.createCell(2).setCellValue(data.getLocalPath());
            }

            // 将Excel写入到字节数组输出流中
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            workbook.write(out);
            ByteArrayInputStream inputStream = new ByteArrayInputStream(out.toByteArray());

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment; filename="+filePath+".xlsx");
            ServletOutputStream outputStream = response.getOutputStream();
            byte[] buffer = new byte[1024]; // 创建缓冲区
            int bytesRead; // 读取的字节数
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
            }
            outputStream.flush(); // 刷新输出流以确保所有数据都已发送到客户端
            inputStream.close(); // 关闭输入流
            outputStream.close(); // 关闭输出流
        }
    }


    // 导入数据包仓库发布商品第六步的上传商品数据
    public static List<DataUploadImageEntity> importExcel(MultipartFile file) throws Exception {

        ArrayList<DataUploadImageEntity> results = new ArrayList<>();

        // 工作薄对象
        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        // 工作表 sheet
        Sheet sheet = workbook.getSheetAt(0);
        // 行
        int rows = sheet.getPhysicalNumberOfRows();
        // 行的头信息，第一行，可以不处理
        for (int i = 1; i < rows; i++) {
            Row row = sheet.getRow(i);
            DataUploadImageEntity dataUploadImage = new DataUploadImageEntity();
            // 第一个单元格
            dataUploadImage.setId((int) row.getCell(0).getNumericCellValue());
            // 第二个单元格
            dataUploadImage.setDetailId((int) row.getCell(1).getNumericCellValue());
            // 第三个单元格
            dataUploadImage.setLocalPath(row.getCell(2).getStringCellValue());
            // 把组装好的对象,存入集合
            results.add(dataUploadImage);
        }
        return results;
    }

}
